unit unittodo;

{$mode ObjFPC}{$H+}

interface

uses
  Classes, SysUtils, SQLDB, DB, Forms, Controls, Graphics, Dialogs, ExtCtrls,
  ComCtrls, StdCtrls, Calendar, Buttons, DBGrids, DateTimePicker, LCLType, unitDMMain;

type

  { TFrmTodo }

  TFrmTodo = class(TForm)
    Bevel1: TBevel;
    Bevel2: TBevel;
    Btn3Month: TBitBtn;
    BtnDatCancel: TBitBtn;
    BtnDatModify: TBitBtn;
    BtnDatNew: TBitBtn;
    BtnDatRemove: TBitBtn;
    BtnDatReset: TBitBtn;
    BtnDatSave: TBitBtn;
    BtnQueryContext: TBitBtn;
    BtnQueryMonth: TBitBtn;
    BtnQueryDay: TBitBtn;
    MyCalendar: TCalendar;
    DSQry: TDataSource;
    DtPickerStartQry: TDateTimePicker;
    DtPickerOverQry: TDateTimePicker;
    Label9: TLabel;
    RadioGroupDatResult: TRadioGroup;
    TodoDBGrid: TDBGrid;
    DTickerDatEnd: TDateTimePicker;
    DTickerDatStart: TDateTimePicker;
    EdtKeywordQry: TEdit;
    EdtDatProject: TEdit;
    EdtDatId: TEdit;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    GroupBox4: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    EdtDatDescription: TMemo;
    LabTips: TLabel;
    PageControl1: TPageControl;
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel5: TPanel;
    Panel6: TPanel;
    Panel7: TPanel;
    Panel8: TPanel;
    RadioGroupResultQry: TRadioGroup;
    Splitter1: TSplitter;
    Splitter2: TSplitter;
    SQLQueryQry: TSQLQuery;
    TabSheetContext: TTabSheet;
    TabSheetFixed: TTabSheet;
    procedure Btn3MonthClick(Sender: TObject);
    procedure BtnDatCancelClick(Sender: TObject);
    procedure BtnDatModifyClick(Sender: TObject);
    procedure BtnDatNewClick(Sender: TObject);
    procedure BtnDatRemoveClick(Sender: TObject);
    procedure BtnDatResetClick(Sender: TObject);
    procedure BtnDatSaveClick(Sender: TObject);
    procedure BtnQueryContextClick(Sender: TObject);
    procedure BtnQueryDayClick(Sender: TObject);
    procedure BtnQueryMonthClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ClearInput();
    procedure QueryList();
    procedure EnableInput(const status: boolean);
    procedure TodoDBGridDblClick(Sender: TObject);
  private

  public

  end;

  procedure InsertIntoSQLiteTable(const Project, Description, Start, Over, Result, Notes: String);
  procedure ModifySQLiteTable(const Id, Project, Description, Start, Over, Result, Notes: String);
  procedure RemoveSQLiteTable(const id: String);

var
  FrmTodo: TFrmTodo;
  optStatus: String;
  FormatSettings: TFormatSettings;

implementation

{$R *.lfm}

{ TFrmTodo }

procedure TFrmTodo.FormCreate(Sender: TObject);
var
  today:TDatetime;
begin

  // 初始化日期和时间格式设置
  // 设置日期分隔符和时间分隔符
  FormatSettings.LongDateFormat := 'yyyy-MM-dd';
  FormatSettings.ShortDateFormat := 'yyyy-MM-dd';
  FormatSettings.LongTimeFormat := 'hh:mm:ss';
  FormatSettings.ShortTimeFormat := 'hh:mm:ss';
  FormatSettings.DateSeparator := '-';
  FormatSettings.timeSeparator := ':';

  // 1.
  today:=now();
  optStatus:='';
  MyCalendar.DateTime:=today;
  DtPickerStartQry.DateTime:=today-3;
  DtPickerOverQry.DateTime:=today;
  DTickerDatStart.DateTime :=  today  - (60 / (24 * 60));
  DTickerDatEnd.DateTime :=  today;

  // 2.
  BtnDatModify.Enabled:=false;
  EnableInput(false);

  // 3.
  try
    SQLQueryQry.DataBase:=DMMain.UMConn;
    SQLQueryQry.Transaction:=DMMain.UMransaction;
  except
    on E: Exception do
    begin
      ShowMessage('请退出当前软件，发生异常：' + E.Message);
    end;
  end;
end;

procedure TFrmTodo.BtnQueryDayClick(Sender: TObject);
var
  Calendar: String;
begin
  Calendar := FormatDateTime('yyyy-mm-dd', MyCalendar.DateTime);

  SQLQueryQry.SQL.Text:=' SELECT id,project_matter,plan_description,start_time,end_time,result_status,notes FROM todo_list '
                      + ' where subStr(start_time,1,10)=' + quotedstr(Calendar);

  SQLQueryQry.Close;
  SQLQueryQry.Open;

  if not SQLQueryQry.Eof then
    GroupBox2.Caption:='[当日查询]记录列表(共' + IntToStr(SQLQueryQry.RecordCount) + '条)'
  else
    GroupBox2.Caption:='[当日查询]记录列表(共0条)';
end;

procedure TFrmTodo.Btn3MonthClick(Sender: TObject);
var
  dtStart,dtOver: TDateTime;
  start,over: String;
begin
  dtOver := IncMonth(MyCalendar.DateTime,1);
  dtStart :=IncMonth(MyCalendar.DateTime,-2);

  over := FormatDateTime('yyyy-mm-01', dtOver) + ' 00:00:00';
  start := FormatDateTime('yyyy-mm-01', dtStart) + ' 00:00:00';

  SQLQueryQry.SQL.Text:=' SELECT id,project_matter,plan_description,start_time,end_time,result_status,notes FROM todo_list '
                      + ' where (start_time<' + quotedstr(over) + ') AND (start_time>='
                      + quotedstr(start) + ')';

  //showmessage(SQLQueryQry.SQL.Text);

  SQLQueryQry.Close;
  SQLQueryQry.Open;

  if not SQLQueryQry.Eof then
    GroupBox2.Caption:='[近三个月查询]记录列表(共' + IntToStr(SQLQueryQry.RecordCount) + '条)'
  else
    GroupBox2.Caption:='[近三个月查询]记录列表(共0条)';
end;

procedure TFrmTodo.BtnDatCancelClick(Sender: TObject);
begin
  optStatus:='';
  BtnDatNew.Enabled:=true;
  BtnDatModify.Enabled:=false;
  EnableInput(false);
end;

procedure TFrmTodo.BtnDatModifyClick(Sender: TObject);
begin
  optStatus := 'Modify';
  BtnDatNew.Enabled:=false;
  EnableInput(true);
  EdtDatProject.SetFocus;
end;

procedure TFrmTodo.BtnDatNewClick(Sender: TObject);
begin
  optStatus := 'New';
  ClearInput();
  EnableInput(true);
  EdtDatProject.SetFocus;
end;

procedure TFrmTodo.BtnDatRemoveClick(Sender: TObject);
var
  Id,tips: String;
begin
  if(optStatus='') and (length(EdtDatId.Text)>0)then
  begin
    if( MessageDlg('您确定删除当前记录？', mtConfirmation, [mbNo, mbYes], 0)=mrNo) then exit;

    Id:=EdtDatId.Text;
    try
      try
        RemoveSQLiteTable(Id);
        tips := '提示：成功删除记录';
        ClearInput();
        QueryList();
      except
        on E: Exception do begin
          showmessage(E.Message);
          tips := '提示：删除记录失败';
        end;
      end;
    finally
      LabTips.Caption:=tips;
      optStatus:='';
      BtnDatNew.Enabled:=true;
      BtnDatModify.Enabled:=false;
    end;

  end
  else Application.MessageBox('请从记录列表中，双击选定所要删除的记录','警告',MB_ICONWARNING);
end;

procedure TFrmTodo.BtnDatResetClick(Sender: TObject);
begin
    ClearInput();
end;

procedure TFrmTodo.BtnDatSaveClick(Sender: TObject);
var
  Id, Project, Description, Start, Over, Result, Notes:String;
  tips: String;
begin
  if(optStatus='')then
  begin
    Application.MessageBox('请确定您的操作，新增或修改？','提示',MB_OK);
    exit;
  end;

  // ''2025-01-17 20'#$E6'32'#$E6'57'' LabTips
  Start := FormatDateTime('yyyy-mm-dd ', DTickerDatStart.DateTime)
          + FormatDateTime('hh', DTickerDatStart.DateTime) + ':'
          + FormatDateTime('nn', DTickerDatStart.DateTime) + ':'
          + FormatDateTime('ss', DTickerDatStart.DateTime);

  Over := FormatDateTime('yyyy-mm-dd ', DTickerDatEnd.DateTime)
          + FormatDateTime('hh', DTickerDatEnd.DateTime) + ':'
          + FormatDateTime('nn', DTickerDatEnd.DateTime) + ':'
          + FormatDateTime('ss', DTickerDatEnd.DateTime);

  // const Id, Project, Description, Start, Over, Result, Notes: String
  // EdtDatID EdtDatProject EdtDatDescription DTickerDatStart DTickerDatEnd RadioGroup2
  Project:=EdtDatProject.Text;
  Description:=EdtDatDescription.Text;
  case RadioGroupDatResult.ItemIndex of
    0: Result:='未完成';
    1: Result:='完成';
    else
      Result:='未完成';
  end;
  Notes:='';

  if(trim(Project)='') or (trim(Description)='') or (trim(Result)='') or (trim(Start)='') or (trim(Over)='') then
  begin
    Application.MessageBox('项目类别、事务描述、开始时间、开始时间、是否完成'#13#10'等栏位的内容不能为空','警告',MB_ICONWARNING);
    exit;
  end;

  if(optStatus='New')then
  begin
    try
      InsertIntoSQLiteTable(Project, Description, Start, Over, Result, Notes);
      tips := '提示：成功新增记录';
    except
      on E: Exception do begin
        showmessage(E.Message);
        tips := '提示：新增记录失败';
      end;
    end;
  end;

  if(optStatus='Modify')then
  begin
    Id := EdtDatId.Text;
    try
      ModifySQLiteTable(Id, Project, Description, Start, Over, Result, Notes);
      tips := '提示：成功修改记录';
    except
      on E: Exception do begin
        showmessage(E.Message);
        tips := '提示：修改记录失败';
      end;
    end;
  end;

  // 清除状态
  optStatus:='';
  BtnDatNew.Enabled:=true;
  BtnDatModify.Enabled:=false;
  LabTips.Caption:=tips;
  EnableInput(false);

  // 刷新数据
  ClearInput();
  QueryList();
end;

procedure TFrmTodo.BtnQueryContextClick(Sender: TObject);
var
  arr: TStringList;
  Keyword,result,Sql,Start,Over: String;
  i: Integer;
begin
  // DtPickerStartQry  DtPickerOverQry EdtKeywordQry  RadioGroupResultQry
  arr := TStringList.Create;
  arr.Delimiter := ' ';
  arr.DelimitedText := EdtKeywordQry.Text;

  Keyword:='';
  for i := 0 to arr.Count-1 do
    if((i+1)=arr.Count)then
      Keyword:= Keyword + ' ((project_matter LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (plan_description LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (notes LIKE ' + QuotedStr('%' + arr[i] +'%') + '))'
    else
      Keyword:= Keyword + ' ((project_matter LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (plan_description LIKE ' + QuotedStr('%' + arr[i] +'%') + ') OR (notes LIKE ' + QuotedStr('%' + arr[i] +'%') + ')) OR ';

  Start:=FormatDateTime('yyyy-mm-dd ', DtPickerStartQry.Date);
  Over:=FormatDateTime('yyyy-mm-dd ', DtPickerOverQry.Date+1);

  case RadioGroupResultQry.ItemIndex of
    0: result:=' ';
    1: result:=' AND (result_status=''完成'')';
    2: result:=' AND (result_status=''未完成'')';
  end;

  Sql:=' SELECT id,project_matter,plan_description,start_time,end_time,result_status,notes FROM todo_list where (1=1)'
     + ' AND (start_time >= ' + QuotedStr(Start) + ') '
     + ' AND (start_time < ' + QuotedStr(Over) + ') ';

  if(length(Keyword)>0) then Sql:=Sql+ ' AND (' + Keyword + ') ';

  if(length(result)>0) then Sql:=Sql+ result;

  Sql:= Sql + ' ORDER BY start_time ASC ';

  SQLQueryQry.SQL.Text:=Sql;

  //showmessage(SQLQueryQry.SQL.Text);

  SQLQueryQry.Close;
  SQLQueryQry.Open;

  if not SQLQueryQry.Eof then
    GroupBox2.Caption:='记录列表(共' + IntToStr(SQLQueryQry.RecordCount) + '条)'
  else
    GroupBox2.Caption:='记录列表(共0条)';

end;

procedure TFrmTodo.BtnQueryMonthClick(Sender: TObject);
var
  Calendar: String;
begin
  Calendar := FormatDateTime('yyyy-mm', MyCalendar.DateTime);

  SQLQueryQry.SQL.Text:=' SELECT id,project_matter,plan_description,start_time,end_time,result_status,notes FROM todo_list '
                      + ' where subStr(start_time,1,7)=' + quotedstr(Calendar);

  SQLQueryQry.Close;
  SQLQueryQry.Open;

  if not SQLQueryQry.Eof then
    GroupBox2.Caption:='[当月查询]记录列表(共' + IntToStr(SQLQueryQry.RecordCount) + '条)'
  else
    GroupBox2.Caption:='[当月查询]记录列表(共0条)';
end;
procedure TFrmTodo.ClearInput();
var
    today: TDateTime;
begin
  // EdtDatId EdtDatProject EdtDatDescription DTickerDatStart DTickerDatEnd RadioGroupDatResult
  today:=now();
  DTickerDatStart.DateTime := today - (60 / (24 * 60));
  DTickerDatEnd.DateTime := today;
  EdtDatId.Text:='';
  EdtDatProject.Text:='';
  EdtDatDescription.Text:='';
  RadioGroupDatResult.ItemIndex:=0;
end;

procedure TFrmTodo.EnableInput(const status: boolean);
begin
  DTickerDatStart.Enabled := status;
  DTickerDatEnd.Enabled := status;
  EdtDatProject.Enabled := status;
  EdtDatDescription.Enabled := status;
  RadioGroupDatResult.Enabled := status;
end;

procedure TFrmTodo.TodoDBGridDblClick(Sender: TObject);
var
  Grid: TDBGrid;
  DataSet: TDataSet;
  Start,Over,Result: String;
  dtStart,dtOver: TDateTime;
begin
  Grid := Sender as TDBGrid;
  DataSet := Grid.DataSource.DataSet;
  if(DataSet.RecordCount>0) and (optStatus='') then
  begin
    EdtDatId.Text:=DataSet.FieldByName('id').AsString;
    EdtDatProject.Text:=DataSet.FieldByName('project_matter').AsString;
    EdtDatDescription.Text:=DataSet.FieldByName('plan_description').AsString;

    Result:=DataSet.FieldByName('result_status').AsString;
    case Result of
      '未完成': RadioGroupDatResult.ItemIndex:=0;
      '完成': RadioGroupDatResult.ItemIndex:=1;
    else
      RadioGroupDatResult.ItemIndex:=0;
    end;

    Start := DataSet.FieldByName('start_time').AsString;
    if (TryStrToDateTime(Start,dtStart,FormatSettings))then DTickerDatStart.DateTime:=dtStart;

    Over := DataSet.FieldByName('end_time').AsString;
    if (TryStrToDateTime(Over,dtOver,FormatSettings))then DTickerDatEnd.DateTime:=dtOver;

    BtnDatNew.Enabled:=false;
    BtnDatModify.Enabled:=true;
  end;
end;

procedure TFrmTodo.QueryList();
begin
  SQLQueryQry.Close;
  SQLQueryQry.Open;

  if not SQLQueryQry.Eof then
    GroupBox1.Caption:='记录列表(共' + IntToStr(SQLQueryQry.RecordCount) + '条)'
  else
    GroupBox1.Caption:='记录列表(共0条)' ;
end;

procedure InsertIntoSQLiteTable(const Project, Description, Start, Over, Result, Notes: String);
var
  SQLQuery: TSQLQuery;
begin
  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := 'INSERT INTO todo_list(project_matter,plan_description,start_time,end_time,result_status,notes) '
                    + 'VALUES(:Project,:Description,:Start,:Over,:Result,:Notes)';

  // 3. 执行插入语句
  try
    try
      if (not DMMain.UMransaction.Active) then DMMain.UMransaction.StartTransaction;
      SQLQuery.ParamByName('Project').AsString := Project;
      SQLQuery.ParamByName('Description').AsString := Description;
      SQLQuery.ParamByName('Start').AsString := Start;
      SQLQuery.ParamByName('Over').AsString := Over;
      SQLQuery.ParamByName('Result').AsString := Result;
      SQLQuery.ParamByName('Notes').AsString := Notes;
      SQLQuery.ExecSQL;
      DMMain.UMransaction.Commit;
    except
      on E: Exception do
      begin
        DMMain.UMransaction.Rollback;
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    // 清理
    SQLQuery.Free;
  end;

end;

procedure ModifySQLiteTable(const Id, Project, Description, Start, Over, Result, Notes: String);
var
  SQLQuery: TSQLQuery;
begin
  // 4. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 5. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := ' UPDATE todo_list SET project_matter=:Project,plan_description=:Description,'
                    +  ' start_time=:Start,end_time=:Over,result_status=:Result,notes=:Notes '
                    +  ' WHERE (id=:id) ';

  // 6. 执行插入语句
  try
    try
      if (not DMMain.UMransaction.Active) then DMMain.UMransaction.StartTransaction;
      SQLQuery.ParamByName('Project').AsString := Project;
      SQLQuery.ParamByName('Description').AsString := Description;
      SQLQuery.ParamByName('Start').AsString := Start;
      SQLQuery.ParamByName('Over').AsString := Over;
      SQLQuery.ParamByName('Result').AsString := Result;
      SQLQuery.ParamByName('Notes').AsString := Notes;
      SQLQuery.ParamByName('id').AsString := Id;
      SQLQuery.ExecSQL;
      DMMain.UMransaction.Commit;
    except
      on E: Exception do
      begin
        DMMain.UMransaction.Rollback;
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    // 清理
    SQLQuery.Free;
  end;

end;

procedure RemoveSQLiteTable(const id: String);
var
  SQLQuery: TSQLQuery;
begin
  // 1. 创建SQL查询
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.DataBase:=DMMain.UMConn;
  SQLQuery.Transaction:=DMMain.UMransaction;

  // 2. 设置要执行的SQL插入语句
  SQLQuery.SQL.Text := 'DELETE FROM todo_list WHERE id=' + QuotedStr(id);

  // 3. 执行插入语句
  try
    try
      if (not DMMain.UMransaction.Active) then DMMain.UMransaction.StartTransaction;
      SQLQuery.ExecSQL;
      DMMain.UMransaction.Commit;
    except
      on E: Exception do
      begin
        DMMain.UMransaction.Rollback;
        ShowMessage('发生异常：' + E.Message);
      end;
    end;
  finally
    // 清理
    SQLQuery.Free;
  end;

end;
end.

